package com.chenxu.gmall.mapper;

import com.chenxu.gmall.bean.VisitorStats;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * Date: 2021/07/20
 * Desc: 访客统计接口
 */
public interface VisitorStatsMapper {
    @Select("select is_new,sum(uv_ct) uv_ct,sum(pv_ct) pv_ct,sum(sv_ct) sv_ct, sum(uj_ct) uj_ct,sum(dur_sum) dur_sum " +
        " from visitor_stats_0709 where toYYYYMMDD(stt)=#{date} group by is_new")
    List<VisitorStats> selectVisitorStatsByNewFlag(int date);

    /**
     *  ClickHouse的函数
     *      -if(条件表达式，满足返回的值，不满足返回的值)
     *      -toHour
     *         将日期转换为小时 （0~23）
     */
    //if(is_new='1', visitor_stats_0709.uv_ct,0)表示如果是新访客，拿到uv值；如果不是新访客，则默认值为0；然后把这些值进行累加；
    //toHour格式是0-23；
    @Select("select  sum(if(is_new='1', visitor_stats_0709.uv_ct,0)) new_uv,toHour(stt) hr," +
        " sum(visitor_stats_0709.uv_ct) uv_ct, sum(pv_ct) pv_ct, sum(uj_ct) uj_ct " +
        " from visitor_stats_0709 where toYYYYMMDD(stt)=#{date} group by toHour(stt) order by toHour(stt)")
    List<VisitorStats> selectVisitorStatsByHr(int date);
}
